A portion of the disclosure of this patent document contains material which is subject to copyright protection. The copyright owner has no objection to the facsimile reproduction by anyone of the patent document or the patent disclosure, as it appears in the Patent and Trademark Office patent file or records, but otherwise reserves all copyright rights whatsoever.
1. The Field of the Invention
The present invention relates generally to computer-implemented database systems. More specifically, the present invention relates to a system and method for specifying custom qualifiers for explain tables in a computer-implemented database system.
2. The Relevant Technology
Databases are computerized information storage and retrieval systems. A Relational Database Management System (RDBMS) is a database system which uses relational techniques for storing and retrieving data. Relational databases are organized into tables consisting of rows (tuples) and columns of data. A database typically includes many tables, and each table includes multiple rows and columns. The tables are conventionally stored in direct access storage devices (DASD), such as magnetic or optical disk drives, for semi-permanent storage.
Generally, users communicate with an RDBMS using a Structured Query Language (SQL) interface. The SQL interface allows users to create, manipulate, and query a database by formulating relational operations on the tables, either interactively, in batch files, or embedded in host languages such as C and COBOL. SQL has evolved into a standard language for RDBMS software and has been adopted as such by both the American National Standards Institute (ANSI) and the International Standards Organization (ISO).
The SQL standard provides that each RDBMS should respond to a particular query in the same way, regardless of the underlying database. However, the method that the RDBMS actually uses to find the requested information in the database is left to the RDBMS. Typically, there is more than one method that can be used by the RDBMS to access the requested data. The RDBMS, therefore, attempts to select the method that minimizes the computer time and resources (i.e. cost) for executing the query.
The RDBMS determines how to execute the SQL statements. The set of steps created by the RDBMS for executing the SQL statements is commonly referred to as the xe2x80x9caccess path.xe2x80x9d In other words, the access path is a sequence of operations used by the RDBMS to obtain the data requested by the SQL query. Depending on the access path, an SQL statement might search an entire table space, or, alternatively, it might use an index. The access path is the key to determining how well an SQL statement performs. The description of the access path is stored in a plan table, which typically stores the access path data for a plurality of SQL statements.
In addition to determining the access path, some databases estimate the cost for executing each SQL statement. The estimated costs are typically stored in a statement table (as in the case of DB2(copyright) for OS/390(copyright)) or a similar table within the database. Like the plan table, the statement table stores the estimated statement costs for a plurality of SQL statements.
Databases also typically include statistics for such database objects as table spaces, indexes, tables, and columns. For example, in the case of a table, the statistical data may include the number of pages that contain rows of the table, the number of rows and columns in the table, as well as various other statistical data. The statistics are typically derived from the RDBMS xe2x80x9ccatalog,xe2x80x9d which is an object that describes the entire database.
The above-described access path data, statement cost data, and object statistics data (referred to collectively as xe2x80x9cquery explain dataxe2x80x9d) assist the user in analyzing and improving the performance of SQL statements. For example, a query with a higher-than-average statement cost might alert the user to inefficiencies in the access path. By viewing the access path data, the user can selectively make changes to the query and/or the database, such as by adding an index in order to avoid a table space scan. The object statistics data similarly assists the user by describing the structure and organization of the database.
Unfortunately, analyzing SQL query performance is currently too time-consuming and complex for the average user. Typical systems include hundreds or thousands of query statements. Identifying the one or more statements that need to be improved can be a difficult task. In conventional systems, the user must manually locate the relevant query explain data in a plurality of tables, e.g. the plan table, the statement table, the function table, and the catalog tables, which can be tedious and time-consuming.
Moreover, each of the tables typically stores information corresponding to many different statements and objects. Consequently, the tables are often very large, making it difficult to locate the desired data. Likewise, the tables are often cryptic and hard to understand, even for database experts. For example, the plan table typically includes sixty or more columns and hundreds or thousands of rows. The access path data is stored in a tabular format, which, although easily understood by the RDBMS, is often too complicated to be effectively analyzed.
Furthermore, the query statements to be filtered are typically included in a number of packages and plans. A package is a collection of query statements found in a single application program. A plan is also a collection of query statements, but may include statements from one or more application programs. In large-scale database systems, the number of plans and packages is correspondingly large. Thus, a difficulty arises when trying to locate a particular package or plan for purposes of analyzing the query statements contained therein.
Similarly, query explain tables can become extremely large. Accordingly, different query explain tables may be used for each package or for each plan. In one arrangement commonly used, each individual user within a database system is assigned a unique label and all packages and plans generated by the user employ one or more query explain tables unique to the user. The label given the user is known as a high level qualifier. The high level qualifier is generally a part of a naming system or convention in which a portion of the label, typically the beginning portion, references the maker or user, and subsequent portions identify the type of the component being labeled.
Currently under this arrangement, query explain tables assigned to a user are automatically assigned the user""s high level qualifier at the time of binding of the query explain tables. When a user wishes to reference query explain data, the query explain program determines who the user is, typically by requesting that the user designating his high level qualifier, and automatically calls up query explain tables and other query explain data corresponding to the user""s high level qualifier.
Problems arise when the user wishes to name a query explain table with a labels other than the user""s high level qualifier. Current query explain programs do not have the flexibility to allow a user to do so, nor do they allow a user to individually access user-named tables. Additionally, if a user wishes to examine query explain data generated by another user, for instance, where a system administrator desires to ascertain where slowdowns in a database are occurring, the user is unable to individually reference the query explain tables generated by others while logged in under the user""s own account. Accordingly, when a user wishes to reference explain tables that were not generated under the user""s high level qualifier or other user code, the user is forced to log out and log in under the account corresponding to the user code of the tables that are desired to be referenced.
In accordance with the above discussion, it should be readily appreciated that a need exists for a query explain system that allows users to designate the labels of query explain tables. What is similarly needed is a manner of allowing users, from within a query explain program, to reference a selected query explain table other than the default table using the particular high level qualifier or other label of the query explain table.
The present invention solves the foregoing problems by providing a system, method, and article of manufacture for specifying a custom qualifier for a query explain table. In disclosed embodiments, query explain tables are assigned custom qualifiers, and users are allowed to reference any query explain tables for which they are granted access privileges by specifying the qualifier or other identifier of the query explain table the user wishes to access.
In one aspect of the invention, an apparatus for accessing a user-selectable query explain table includes therein modules of operational data and executable code for execution by the processor. In one embodiment, the modules include a query explain program for relaying information regarding query execution to a user. Preferably, the query explain program is configured to access one or more query explain tables generated in accordance with operation of the database system.
Preferably, the modules also comprise an explain table qualifier designation module communicating with the query explain program and configured to receive a user designation of a selected query explain table to be accessed by the query explain program. The modules may also comprise an explain table access module configured to access the selected query explain table in response to the user designation.
In one embodiment, the user designation of the selected query explain table comprises the designation of a high level qualifier uniquely identifying the selected query explain table. The high level qualifier may identify a maker of the explain table.
Additionally, the explain table qualifier designation module may comprise a window accessible from the query explain program. Preferably, the window comprises a control mechanism for receiving the user designation of a selected query explain table.
The query explain table may comprise a plan table, a statement table, a function table, or other types of query explain data. The query explain table is preferably accessed by the query explain program for a plurality of different functions including displaying query access path data to the user.
In another aspect of the invention, a method of accessing a user-selectable query explain table includes a step of receiving within a query explain program a user designation of a selected query explain table generated in accordance with operation of the database system. The method also preferably comprises referencing the selected query explain table in response to receiving the user designation of the selected query explain table.
The method may also comprise generating the selected query explain table and assigning the query explain table a user-generated high level qualifier. In one embodiment, the query explain table is selected from the group consisting of a plan table, a statement table, and a function table. Additionally, referencing the selected query explain table may comprise automatically referencing the selected query explain table in order to achieve a function of the query explain program. The function may comprise displaying query access path data to the user.
The method may further comprise generating a graphical user interface (GUI) window accessible from the query explain program, the GUI window comprising a control mechanism for receiving the user designation of a selected query explain table. In one embodiment, receiving a user designation of a selected query explain table comprises receiving a high level qualifier uniquely identifying the selected query explain table. Additionally, the high level qualifier may designate a maker of the query explain table.
In yet another aspect of the invention, an article of manufacture comprises a program storage medium readable by a processor and embodying one or more instructions executable by the processor to perform the above-described method for filtering a plurality of groups of query statements according to associated identification data.
These and other objects, features, and advantages of the present invention will become more fully apparent from the following description and appended claims, or may be learned by the practice of the invention as set forth hereinafter.